package rubix.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;

public class InventoryManager {

    private static final String TBLNAME = "inventory";

    private static void handleSQLException(SQLException ex, String sql, String... parameters) {
        String msg = "Unable to access data; SQL=" + sql + "\n";

        for (String parameter : parameters) {
            msg += "," + parameter;
        }

        Logger.getLogger(InventoryManager.class.getName()).log(Level.SEVERE, msg, ex);
        throw new RuntimeException(msg, ex);
    }
    //method
    public static void prepareTables(){
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "";
        
        try {
            conn = ConnectionManager.getConnection();            
            
            sql = "DROP TABLE IF EXISTS inventory";
            stmt = conn.prepareStatement(sql);
            stmt.executeUpdate();
            
            sql = "CREATE TABLE IF NOT EXISTS inventory("
                    + "itemtype VARCHAR(25) NOT NULL,"
                    + "itemcategory VARCHAR(15) NOT NULL,"
                    + "itemid VARCHAR(25) NOT NULL,"
                    + "description1 VARCHAR(50) NOT NULL,"                     					
                    + "description2 VARCHAR(50) NOT NULL," 
                    + "PRIMARY KEY (itemtype)"
                    + ")";
            stmt = conn.prepareStatement(sql);
            stmt.executeUpdate();
        } catch (SQLException ex) {
            handleSQLException(ex, sql, "prepareTables");
        } finally {
            ConnectionManager.close(conn, stmt);
        }
    }    
    
    public static void addInventory(Inventory inventory) {
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "";
        
        String itemType = inventory.getItemType();
        String itemCategory = inventory.getItemCategory();
        String itemId = inventory.getItemId();
        String itemDescription1 = inventory.getItemDescription1();
        String itemDescription2 = inventory.getItemDescription2();
        
        try {
            conn = ConnectionManager.getConnection();
            
            sql = "INSERT INTO " + TBLNAME + " (itemtype,itemcategory,itemid,description1,description2) VALUES (?,?,?,?,?)";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, itemType);
            stmt.setString(2, itemCategory);
            stmt.setString(3, itemId);
            stmt.setString(4, itemDescription1);
            stmt.setString(4, itemDescription2);
            
            stmt.executeUpdate();
        } catch (SQLException ex) {
            handleSQLException(ex, sql, "itemtype=" + itemType + ",itemcategory=" + itemCategory + ",itemid=" 
                   + itemId + ",description1=" + itemDescription1 + ",description2=" + itemDescription2);
        } finally {
            ConnectionManager.close(conn, stmt);
        }
    }
    
    public static ArrayList<Inventory> retrieveAll() {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String sql = "";
        ArrayList<Inventory> results = new ArrayList<Inventory>();

        try {
            conn = ConnectionManager.getConnection();

            sql = "SELECT itemtype,itemcategory,itemid,description1,description2 FROM " + TBLNAME;
            stmt = conn.prepareStatement(sql);

            rs = stmt.executeQuery();
            while (rs.next()) {
                String itemType = rs.getString("itemtype");
                String itemCategory = rs.getString("itemcategory");
                String itemId = rs.getString("itemid");
                String itemDescription1 = rs.getString("description1");
                String itemDescription2 = rs.getString("description2");

                results.add(new Inventory(itemType,itemCategory,itemId,itemDescription1,itemDescription2));
            }
        } catch (SQLException ex) {
            handleSQLException(ex, sql);
        } finally {
            ConnectionManager.close(conn, stmt, rs);
        }
        return results;
    }

    public static ArrayList<Inventory> retrieveInventoryList(String itemType) {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String sql = "";
        ArrayList<Inventory> results = new ArrayList<Inventory>();

        try {
            conn = ConnectionManager.getConnection();

            sql = "SELECT itemcategory,itemid,description1,description2 FROM " + TBLNAME + " WHERE itemtype = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, itemType);

            rs = stmt.executeQuery();

            while (rs.next()) {
                String itemCategory = rs.getString("itemcategory");
                String itemId = rs.getString("itemid");
                String itemDescription1 = rs.getString("description1");
                String itemDescription2 = rs.getString("descrption2");

                results.add(new Inventory(itemType,itemCategory,itemId,itemDescription1,itemDescription2));
            }
        } catch (SQLException ex) {
            handleSQLException(ex, sql, "itemtype=" + itemType);
        } finally {
            ConnectionManager.close(conn, stmt, rs);
        }
        return results;
    }

    
}